Table Truncation Teardown

The book has now been published and the content of this chapter has likely changed substanstially.
Please see page 661 of xUnit Test Patterns for the latest information.
How do we tear down the Test Fixture when it is in a relational database?

Truncate the tables modified during the test to tear down the fixture.

Sketch Table Truncation Teardown embedded from Table Truncation Teardown.gif

A large part of making tests repeatable and robust is ensuring that the test fixture is torn down after each test. Leftover objects and database records, open files and connections can at best cause performance degradations and at worst cause tests to fail or systems to crash. While some of these resources may be cleaned up automatically by garbage collection, others may be left hanging if they are not torn down explicitly.

Writing tearDown code that can be relied upon to clean up properly in all possible circumstances is challenging and time-consuming. It involves understanding what could be left over for each possible outcome of the test and writing code to deal with it. This Complex Teardown (see Obscure Test on page X) introduces a fair bit of Conditional Test Logic (page X) and worst of all Untestable Test Code (see Hard to Test Code on page X).

When testing a system that uses a relational database, we can take advantage of the capabilities of the database by using the TRUNCATE command to remove all the data from a table we have modified.

How It Works

When we are done with a persitent fixture we issue a TRUNCATE command for each table in the fixture. This blasts all the data out of the tables very efficiently with no side effects (triggers, etc.).

When To Use It

We can often use Table Truncation Teardown when we are using a Persistent Fresh Fixture (see Fresh Fixture on page X) strategy with a system under test (SUT) that includes a database but it is not usually our first choice. That distinction goes to Transaction Rollback Teardown (page X). It is, however, a better choice for use with a Shared Fixture (page X) which must, by definition, outlive any one test. This is because using Transaction Rollback Teardown with a Shared Fixture would require a very long-running transaction. While not impossible, it is troublesome.

Before we can use Table Truncation Teardown we need to satisfy a couple of criteria. The first requirement for using Table Truncation Teardown is that we really want all the data in the affected tables removed.

The second requirement is that each Test Runner (page X) has its own Database Sandbox (page X). Table Truncation Teardown will not work if we are using a Database Partitioning Scheme (see Database Sandbox) to isolate users or tests from each other. It is ideally suited for use with a DB Schema per TestRunner (see Database Sandbox) especially when we are implementing an Immutable Shared Fixture (see Shared Fixture) as a separate shared schema in the database. This allows us to blast away all the Fresh Fixture data in our own Database Sandbox without affecting the Immutable Shared Fixture.

If we are not using a transactional database the closest approximation is Automated Teardown (page X) since it only deletes those records that were created by the test. It does not depend on the database transactions to do the work for it but it does involve more development work on our part. We can also avoid the need to do tear down by using Delta Assertions (page X).

Implementation Notes

Besides the usual "where do we put the tear down code?" decision, implementation of Table Truncation Teardown also needs to deal with:

How do we actually delete the data; what database commands do we use?
How do we deal with foreign key constraints and triggers.
How do we ensure consistency when we are using an ORM?

Some databases support the TRUNCATE command directly. Where this is the case the obvious choice is to use it. Oracle is one example of a database that supports TRUNCATE. Otherwise, we may have to use DELETE * FROM table-name instead. The TRUNCATE or DELETEcommands can be issued using Inline Teardown (page X) (called from within each Test Method (page X)) or Implicit Teardown (page X) (called from the tearDown method.) Some people prefer to use it with Lazy Teardown because that ensures the tables are empty at the beginning of the test that would be affected by the extraneous data.

Database foreign key constraints can be a problem for Table Truncation Teardown if our database does not offer something similar to Oracle's "cascade constraints" option. In Oracle, if the command to truncate a table includes the "cascade constraints" option then rows dependent on the truncated table rows are also deleted. If our database does not cascade deletes then we will have to ensure that our tables are truncated in the order required by the schema. Schema changes can invalidate this order and that will result in failures in the tearDown code. Fortunately, such failures are pretty easy to detect because a test error tells us that our tearDown needs adjusting. Correction is fairly straightforward; typically, we just need to reorder the TRUNCATE commands. We could, of course, come up with a way to issue the TRUNCATE commands in the correct order dynamically based on the dependencies between the tables. Usually, it is enough to encapsulate this trunation logic behind a Test Utility Method (page X).

If we want to avoid the side-effects of triggers, etc. for databases where TRUNCATE is not supported we can disable the constraints and/or triggers for the duration of the test. We should only do this if we have other tests that exercise the SUT with the constraints/triggers in place.

If we are using an object-relational mapping (ORM) layer such as Toplink, (n)Hibernate or EJB 3.0, we may need to force the ORM to clear its cache of objects already read from the database so that subsequent object lookups do not find the recently deleted objects. For example, NHibernate provides the ClearAllCaches method on the TransactionManager.

Variation: Lazy Teardown

A tear down technique that only works with a few styles of Shared Fixtures is Lazy Teardown. The reason is that the fixture must be destroyable at an arbitrary point in time. This implies that we cannot depend on "remembering" what needs to be torn down; it must be obvious without any "memory". Table Truncation Teardown fits the bill because how we tear down is exactly the same whenever we choose to do it. We simply issue the table truncation commands during fixture setup before setting up the new fixture.

Motivating Example

Here is an example of a test that is attempting to use Guaranteed Inline Teardown (see Inline Teardown) to remove all the records it created.

      [Test]
      public void TestGetFlightsByOrigin_NoInboundFlights()
      {
         // Fixture setup
         long OutboundAirport = CreateTestAirport("1OF");
         long InboundAirport = CreateTestAirport("1IF");
         FlightDto ExpFlightDto = null;
         try
         {     
            ExpFlightDto = CreateTestFlight(OutboundAirport, InboundAirport);
            // Exercise System
            IList FlightsAtDestination1 =
               Facade.GetFlightsByOriginAirport( InboundAirport);
            // Verify Outcome
            Assert.AreEqual( 0, FlightsAtDestination1.Count );
         }
         finally
         {
            Facade.RemoveFlight( ExpFlightDto.FlightNumber );
            Facade.RemoveAirport( OutboundAirport );
            Facade.RemoveAirport( InboundAirport );
         }
      }
Example NaiveMultiResourceGuaranteedCsTeardown embedded from CSharp/Exercise-06-SuiteFixtureSetup/Com/Clrstream/FlightBooking/Services/Test/InlineTeardownExampleTest.cs

This code is neither easy to write nor is it even correct! (See Inline Teardown for an explanation of what is wrong here.) Trying to keep track of all the objects the SUT has created and then tearing them down one by one in a safe manner is very tricky.

Refactoring Notes

We can avoid most of the issues with coordinating Inline Teardown of multiple resources in a safe way by using Table Truncation Teardown and blasting away all the airports in one fell swoop. (This assumes that we start with no airports and want to end with no airports. If we only wanted to delete these specific airports, we cannot use Table Truncation Teardown.) Most of the refactoring work is to delete the existing tear down code from the finally clause and inserting a call to cleanDatabase. Then we implement this method using the truncation commands.

Example: Table Trunctation (Delegated) Teardown Test

This is what the test looks like when we are done:

      public void TestGetFlightsByOrigin_NoInboundFlight_TTTD()
      {
         // Fixture setup
         long OutboundAirport = CreateTestAirport("1OF");
         long InboundAirport = 0;
         FlightDto ExpectedFlightDto = null;
         try
         {
            InboundAirport = CreateTestAirport("1IF");
            ExpectedFlightDto = CreateTestFlight( OutboundAirport,InboundAirport);
            // Exercise System
            IList FlightsAtDestination1 = Facade.GetFlightsByOriginAirport(InboundAirport);
            // Verify Outcome
            Assert.AreEqual(0,FlightsAtDestination1.Count);
         }
         finally
         {
            CleanDatabase();          
         }
      }
Example TableTruncationCsTeardown embedded from CSharp/Exercise-06-SuiteFixtureSetup/Com/Clrstream/FlightBooking/Services/Test/InlineTeardownExampleTest.cs

I have used Delegated Teardown (see Inline Teardown) in this example to keep the tear down code visible but normally I would use Implicit Teardown by putting this into the tearDown method. The try/catch ensures that cleanDatabase gets run but it does not ensure that a failure inside cleanDatabase will not prevent the tear down from completing.

Example: Lazy Teardown Test

Here is the same example converted to use Lazy Teardown:

      [Test]
      public void TestGetFlightsByOrigin_NoInboundFlight_LTD()
      {
         // Lazy TearDown:
         CleanDatabase();
         // Fixture setup
         long OutboundAirport = CreateTestAirport("1OF");
         long InboundAirport = 0;
         FlightDto ExpectedFlightDto = null;
         InboundAirport = CreateTestAirport("1IF");
         ExpectedFlightDto = CreateTestFlight( OutboundAirport, InboundAirport);
         // Exercise System
         IList FlightsAtDestination1 = Facade.GetFlightsByOriginAirport(InboundAirport);
         // Verify Outcome
         Assert.AreEqual(0,FlightsAtDestination1.Count);
      }
Example TableTruncationTeardown-LazyCs embedded from CSharp/Exercise-06-SuiteFixtureSetup/Com/Clrstream/FlightBooking/Services/Test/InlineTeardownExampleTest.cs

By moving the call to cleanDatabase to the front of the Test Method we are ensuring that the database is in the state we expect it. This cleans up whatever the last test did regardless of whether it did proper tear down or not. It also takes care of anything added to the relevant tables since the last test was run. It has the added benefit of removing the need for the try/finally construct thus making the test simpler and easier to understand.

Example: Table Truncation Tear Down using SQL

Here is the implementation of the cleanDatabase method using SQL statements constructed within the code;

   public static void CleanDatabase() {
      string[] tablesToTruncate = new string[] {"Airport","City","Airline_Cd","Flight"};
         IDbConnection conn = getCurrentConnection();
      IDbTransaction txn = conn.BeginTransaction();
      try {
         foreach (string eachTableToTruncate in tablesToTruncate)
         {
            TruncateTable(txn, eachTableToTruncate);
         }
         txn.Commit();
         conn.Close();
      } catch (Exception e) {
         txn.Rollback();
      } finally {
         conn.Close();
      }
   }

   private static void TruncateTable( IDbTransaction txn,  string tableName)
   {
      const string C_DELETE_SQL = "DELETE FROM {0}";
        IDbCommand cmd = txn.Connection.CreateCommand();
      cmd.Transaction = txn;
      cmd.CommandText = string.Format(C_DELETE_SQL, tableName);
    cmd.ExecuteNonQuery();
   }
Example TableTruncationUsingSql embedded from CSharp/Gregs Examples.cs

Because we are using SQL Server as the database, we had to implement our own TruncateTable method that issues a "Delete * from ..." SQL command. We would not have to do this if our database implemented TRUNCATE directly.

Example: Table Truncation Tear Down using ORM

Here is the implementation of the cleanDatabase method using NHibernate, an ORM layer:

   public static void CleanDatabase() {
      ISession session = TransactionManager.Instance.CurrentSession;
      TransactionManager.Instance.BeginTransaction();
      try { // We only need to delete the root classes because
          // cascade rules will delete all related child entities
          session.Delete("from Airport");
          session.Delete("from City");
          session.Flush();
                  TransactionManager.Instance.Commit();
      } catch (Exception e) { Console.Write(e);
          throw e;
      } finally { TransactionManager.Instance.CloseSession();
      }
   }
Example TableTruncationViaORM embedded from CSharp/Gregs Examples.cs

When using an ORM, we are reading and writing and deleting domain objects; the tool determines what underlying tables they map to and takes the appropriate actions. Because we have chosen to make City and Airport "root" (or parent) objects, any subordinate (child) objects are deleted automatically when the root is deleted. This further decouples us from the details of the table implementations.

Page generated at Wed Feb 09 16:39:34 +1100 2011